import sqlite3
import os
from pathlib import Path

def create_sample_database():
    """创建示例数据库用于测试"""
    # 确保目录存在
    db_dir = Path(__file__).parent.parent.parent / "dataset" / "sqlite"
    db_dir.mkdir(parents=True, exist_ok=True)
    
    # 创建示例数据库
    db_path = db_dir / "sample.db"
    
    # 连接数据库
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 创建示例表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            age INTEGER,
            department TEXT
        )
    ''')
    
    # 插入示例数据
    sample_users = [
        ("张三", "zhangsan@example.com", 28, "技术部"),
        ("李四", "lisi@example.com", 32, "销售部"),
        ("王五", "wangwu@example.com", 25, "市场部"),
        ("赵六", "zhaoliu@example.com", 35, "技术部"),
        ("钱七", "qianqi@example.com", 29, "人事部")
    ]
    
    cursor.executemany(
        "INSERT OR IGNORE INTO users (name, email, age, department) VALUES (?, ?, ?, ?)",
        sample_users
    )
    
    # 创建订单表示例
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            product_name TEXT NOT NULL,
            amount REAL,
            order_date TEXT,
            FOREIGN KEY (user_id) REFERENCES users (id)
        )
    ''')
    
    # 插入示例订单数据
    sample_orders = [
        (1, "笔记本电脑", 5999.99, "2023-01-15"),
        (2, "手机", 3999.99, "2023-02-20"),
        (3, "平板电脑", 2999.99, "2023-03-10"),
        (1, "鼠标", 99.99, "2023-04-05"),
        (4, "键盘", 199.99, "2023-05-12")
    ]
    
    cursor.executemany(
        "INSERT OR IGNORE INTO orders (user_id, product_name, amount, order_date) VALUES (?, ?, ?, ?)",
        sample_orders
    )
    
    # 提交更改并关闭连接
    conn.commit()
    conn.close()
    
    print(f"示例数据库已创建: {db_path}")

if __name__ == "__main__":
    create_sample_database()